Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# NASKIP2

The NASKIP2 option controls how `NA` values are treated in arithmetic operations with the `+` (plus) and `-` (minus) operators. The result is `NA` when any operand is `NA` unless NASKIP2 is set to `YES`.

See also: $NATRIGGER, NASKIP, and NASPELL

Data type

BOOLEAN

Syntax

NASKIP2 = YES|NO

Arguments

YES

  Zeroes are substituted for `NA` values in arithmetic operations using the `+` (plus) and `-` (minus) operators. The two special cases of `NA` `+` `NA` and `NA` `-` `NA` both result in `NA`.

NO

  (Default) `NA` values are treated as NAs in arithmetic operations using the `+` (plus) and `-` (minus) operators. When any of the operands being considered is `NA`, the arithmetic operation evaluates to `NA`.

Notes

Operators in Function Arguments

NASKIP2 is independent of NASKIP. NASKIP2 applies only to arithmetic operations with the `+` (plus) and `-` (minus) operators. NASKIP applies only to aggregation functions. However, when an expression argument to an aggregation function contains a`+` (plus) and `-` (minus) operator, the results of the calculation depend on both NASKIP and NASKIP2. See Example 18-16, "Effects of NASKIP and NASKIP2 When an Expression in an Aggregation Function Contains a Negative Values".

How NASKIP2 Works

The following four lines show four steps in the evaluation of a complex expression that contains NAs when NASKIP2 is set to `YES`.

    3 * (NA + NA) - 5 * (NA + 3)
       3 * NA     -    5 *  3
         NA       -      15
                 -15

Examples

Example 18-16 Effects of NASKIP and NASKIP2 When an Expression in an Aggregation Function Contains a Negative Values

In the following examples, INTEGER variables `X` and `Z`, dimensioned by the INTEGER dimension INTDIM, have the values shown in the second and third columns of the report. The sum of X + Z is given for each combination of NASKIP and NASKIP2 settings, starting with their defaults. The example also shows that when the `+` (plus) operator is used in the expression argument to the TOTAL function, the results that are returned by TOTAL depend on the settings of both NASKIP and NASKIP2.

- NASKIP Set to YES, NASKIP2 Set to NO

  In this example, NASKIP is set to `YES`, which means `NA` values are ignored by the TOTAL function. NASKIP2 is set to `NO`, which means that the result of a `+` (plus) operation will be `NA` when any of the operands are `NA`.

      NASKIP = YES
      NASKIP2 = NO
      COLWIDTH = 5
      REPORT LEFT W 6 DOWN intdim x, z, x + z

  These statements produce the following output. With NASKIP2 set to `NO`, the expression X + Z evaluates to `NA` when either X or Z is `NA`.

      INTDIM   X     Z   x + z
      ------ ----- ----- -----
      1         NA     2    NA
      2          3    NA    NA
      3          7     6    13

  The following statement uses a `+` (plus) operator within the expression argument to the TOTAL function.

      SHOW TOTAL(x + z)

  This statement produces the following result.

      13

  The next statement uses the `+` (plus) operator to add the results that are returned by two TOTAL functions.

      SHOW TOTAL(x) + TOTAL(z)

  This statement produces the following result.

      18

- NASKIP Set to YES, NASKIP2 Set to YES

  In this example, NASKIP is set to `YES`, which means `NA` values are ignored by the TOTAL function. NASKIP2 is set to `YES`, which means that `NA` values are ignored by the `+` (plus) operator

      NASKIP = YES
      NASKIP2 = YES
      REPORT LEFT W 6 DOWN intdim x, z, x + z

  These statements produce the following output. With NASKIP2 set to `YES`, `NA` values are ignored when the expression `X` `+` `Z` is evaluated.

      INTDIM   X     Z   X + Z
      ------ ----- ----- -----
      1         NA     2     2
      2          3    NA     3
      3          7     6    13

  The following statement uses a `+` (plus) operator within the expression argument to the TOTAL function.

      SHOW TOTAL(x + z)

  This statement produces the following result.

      18

  The next statement uses the `+` (plus) operator to add the results that are returned by two TOTAL functions.

      SHOW TOTAL(x) + TOTAL(z)

  This statement produces the following result.

      18

- NASKIP Set to NO, NASKIP2 Set to YES

  In this example, NASKIP is set to `NO`, which means that when any values considered by the TOTAL function are `NA`, TOTAL will return `NA`. NASKIP2 is set to `YES`, which means that `NA` values are ignored by the `+` (plus) operator.

      NASKIP = NO
      NASKIP2 = YES
      REPORT LEFT W 6 DOWN intdim x, z, x + z

  These statements produce the following result.

      INTDIM   X     Z   X + Z
      ------ ----- ----- -----
      1         NA     2     2
      2          3    NA     3
      3          7     6    13

  The following statement uses a `+` (plus) operator within the expression argument to the TOTAL function.

      SHOW TOTAL(x + z)

  This statement produces the following result.

      18

  The next statement uses the `+` (plus) operator to add the results that are returned by two TOTAL functions.

      SHOW TOTAL(x) + TOTAL(z)

  This statement produces the following result.

      NA

- NASKIP Set to NO, NASKIP Set to NO

  In this example, NASKIP is again set to `NO`, which means that when any values considered by the TOTAL function are `NA`, TOTAL will return `NA`. NASKIP2 is also set to `NO`, which means that the result of a `+` (plus) operation will be `NA` when any of the operands are `NA`.

      NASKIP = NO
      NASKIP2 = NO
      REPORT LEFT W 6 DOWN intdim x, z, x + z

  These statements produce the following result.

      INTDIM   X     Z   X + Z
      ------ ----- ----- -----
      1         NA     2    NA
      2          3    NA    NA
      3          7     6    13

  The following statement uses a `+` (plus) operator within the expression argument to the TOTAL function.

      SHOW TOTAL(x + z)

  This statement produces the following result.

      NA

  The next statement uses the `+` (plus) operator to add the results that are returned by two TOTAL functions.

      SHOW TOTAL(x) + TOTAL(z)

  This statement produces the following result.

      NA